CREATE VIEW [dbo].[vGiftHistory] AS
     SELECT  d.[OriginalTransaction],
                 d.[Fund],
                 d.[Campaign],
                 d.[Appeal],
                 DATEPART(year,d.[TransactionDate]) AS CalendarYear,  
                 d.[FiscalYear],
                 d.[ID],
                 d.[TransactionDate],
                 d.[DateReceived],     
                 CASE
                       WHEN (SELECT COUNT(v.OriginalTransaction) from vGiftHistoryBase v where d.OriginalTransaction = v.OriginalTransaction and d.ID = v.ID) > 1 then 'Split-' + d.GiftType
                       ELSE d.GiftType END AS GiftType,
                 d.[SolicitorID],  
                 CASE
                       WHEN d.[GiftType] = 'Soft Credit' THEN 0
                       ELSE d.[Amount] END AS Amount,            
                 CASE
                       WHEN d.[GiftType] = 'Soft Credit' THEN 0
                       ELSE SUM(ISNULL(g.[Amount],0)) END AS Received,
                 (d.Amount-(CASE
                       WHEN d.[GiftType] = 'Soft Credit' THEN 0
                       ELSE SUM(ISNULL(g.[Amount],0)) END)) AS Balance,
                  d.[SoftCreditAmount] AS SoftCredit,
                  d.[SoftCreditDonorID],
                 (d.Amount + d.[SoftCreditAmount]) AS TotalCredits,
                  g.MemorialNameText,
                  g.MemorialTributeType,
                  g.MemorialTributeMessage,
                  g.TributeNotificationContactID                           
       FROM [dbo].[vGiftHistoryBase] d
              LEFT OUTER JOIN [dbo].[vGiftsReceivedBase] g ON d.[OriginalTransaction] = g.[OriginalTransaction] and d.[Fund] = g.[Fund]
    GROUP BY  d.[OriginalTransaction],
             d.[SourceSystem],
             d.[Fund],
             d.[Campaign],                 
             d.[Appeal],            
             d.[PaymentType],            
             d.[TransactionDate],
             d.[FiscalYear],
             d.[DateReceived],                 
             d.[ID],
             d.[GiftType],
             d.[SolicitorID],
             d.[Amount],
             d.[SoftCreditAmount],
             d.[SoftCreditDonorID],
             g.MemorialNameText,
             g.MemorialTributeType,
             g.MemorialTributeMessage,
             g.TributeNotificationContactID







GO